
#include <QSqlQuery>
#include <QSqlRecord>
#include <QVariant>
#include <QMessageBox>
#include "../entity/money.h"
#include "specificsearch.h"
#include "categorydao.h"
#include "moneydao.h"
#include <QDebug>

/** ABOUT MONEY <-> CATEGORY **/
QList<MoneyCategory*> getAllCategoryByMoney(Money* money, QList<MoneyCategory*> listAllMoneyCategory) {
    if (!money)
        return QList<MoneyCategory*>();

    qDebug() << __PRETTY_FUNCTION__;

    QList<MoneyCategory*> listCategory;

    QString strQuery = "SELECT id_category as id_category FROM money_category "
                       " WHERE id_money = :id_money";
    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":id_money", money->getIdMoney());

    query.exec();

    while (query.next()) {
        MoneyCategory* cat = searchForCategory(listAllMoneyCategory,
                                              query.value(query.record().indexOf("id_category")).toInt());
        listCategory.append(cat);
    }

    return listCategory;
}

bool deleteAllCategoryByMoney(Money* money) {
    if (!money)
        return false;

    qDebug() << __PRETTY_FUNCTION__;

    QString strQuery = " DELETE FROM money_category "
               " WHERE id_money = :id_money";

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":id_money", money->getIdMoney());
    return query.exec();
}

bool registerCategoryByMoney(int id_category, QString timestamp) {
    qDebug() << __PRETTY_FUNCTION__;

    QString strQuery = "INSERT INTO money_category (id_money, id_category) "
                       "VALUES ((SELECT money.id_money FROM money money WHERE money.timestamp = :timestamp "
                       "), :id_category)";
    QSqlQuery query;
    query.prepare(strQuery);

    query.bindValue(":id_category", id_category);
    query.bindValue(":timestamp", timestamp);
    return query.exec();
}

bool registerAllCategoryByMoney(Money* money) {
    if (!money)
        return false;

    qDebug() << __PRETTY_FUNCTION__;

    foreach (MoneyCategory* category, money->getListMoneyCategory())
        if (!registerCategoryByMoney(category->id(), money->getTimestamp()))
            return false;
    return true;
}

/** ABOUT MONEY **/

Money* getMoney(Money* incomplete_money, QList<MoneyCategory*> listAllMoneyCategory, int id_user)
{

    qDebug() << __PRETTY_FUNCTION__;

    QString strQuerySelect = " SELECT money.id_money, type_money, amount, dt_transaction, description, timestamp "
                             " as id_money, type_money, amount, dt_transaction, description, timestamp ";
    QString strQueryFrom = " FROM ";
    strQueryFrom = strQueryFrom + " money money ";

    QString strQueryCondition = " WHERE money.id_user = :id_user AND timestamp LIKE :timestamp ";
    QString strQuery = strQuerySelect + strQueryFrom + strQueryCondition;

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":id_user", id_user);
    query.bindValue(":timestamp", incomplete_money->getTimestamp());

    query.exec();

    Money* money;

    if(query.next()) {
        money = new Money();
        money->setIdMoney(query.value(query.record().indexOf("id_money")).toInt());
        money->setTypeMoney(query.value(query.record().indexOf("type_money")).toInt() == 0 ?
                           Money::type_in : Money::type_out );
        money->setAmount((float)query.value(query.record().indexOf("amount")).toDouble());
        money->setDtTransaction(query.value(query.record().indexOf("dt_transaction")).toDate());
        money->setDescription(query.value(query.record().indexOf("description")).toString());
        money->setListMoneyCategory(getAllCategoryByMoney(money, listAllMoneyCategory));
        money->setTimestamp(query.value(query.record().indexOf("timestamp")).toString());
    } else {
        qDebug() << "It didn't find the Money";
    }
    return money;
}

QList<Money*> getAllMoney(int id_user, QList<MoneyCategory*> listAllMoneyCategory, SpecificSearch* ss, Money::money_types typeMoney)
{
    qDebug() << __PRETTY_FUNCTION__;

    QString strQuery = " ";

    QString strQuerySelect = " SELECT money.id_money, type_money, amount, dt_transaction, description, timestamp "
                             " as id_money, type_money, amount, dt_transaction, description, timestamp ";
    QString strQueryFrom = " FROM ";
    strQueryFrom = strQueryFrom + " money money ";


    QString strQueryCondition = " WHERE money.id_user = :id_user ";

    QString strQueryOrder = " order by money.dt_transaction ";

    QSqlQuery query;

    if (typeMoney == Money::type_none ) {

        qDebug() << __PRETTY_FUNCTION__ << "Search ALL";

        strQuery = strQuerySelect + strQueryFrom;

        if (ss && ss->getNumberActiveFilters() > 0) {
            if (ss->isListMoneyCategoryUsed())
                strQueryFrom = strQueryFrom + "," + "money_category cat ";
            strQuery = strQuerySelect + strQueryFrom;
            strQuery = strQuery + strQueryCondition + " AND " + ss->getConditions();

            strQuery = strQuery + strQueryOrder;
            query.prepare(strQuery);
            query = ss->prepareQuery(query);
        } else {
            strQuery = strQuery + strQueryCondition;
            strQuery = strQuery + strQueryOrder;
            query.prepare(strQuery);
        }

        qDebug() << strQuery;

    } else {
        strQueryCondition = strQueryCondition + "AND money.type_money = :type_money ";
        strQuery = strQuerySelect + strQueryFrom + strQueryCondition;
        if (ss && ss->getNumberActiveFilters() > 0) {
            if (ss->isListMoneyCategoryUsed())
                strQueryFrom = strQueryFrom + "," + " money_category cat ";
            strQuery = strQuerySelect + strQueryFrom + strQueryCondition;
            strQuery = strQuery + " AND " + ss->getConditions();
            query.prepare(strQuery);
            strQuery = strQuery + strQueryOrder;
            query = ss->prepareQuery(query);
        } else {
            strQuery = strQuery + strQueryOrder;
            query.prepare(strQuery);
        }
        qDebug() << strQuery;
        qDebug() << __PRETTY_FUNCTION__ << "Binding TypeMoney " << typeMoney;
        query.bindValue(":type_money", typeMoney == Money::type_in ? 0 : 1);
    }

    qDebug() << __PRETTY_FUNCTION__ << "Binding idUser " << id_user;
    query.bindValue(":id_user", id_user);
    query.exec();

    //QList<MoneyCategory*> listAllMoneyCategory = getAllMoneyCategory(id_user);
    QList<Money*> listMoney;

    while (query.next()) {
        Money* money = new Money;

        money->setIdMoney(query.value(query.record().indexOf("id_money")).toInt());
        money->setTypeMoney(query.value(query.record().indexOf("type_money")).toInt() == 0 ?
                           Money::type_in : Money::type_out );
        money->setAmount((float)query.value(query.record().indexOf("amount")).toDouble());
        money->setDtTransaction(query.value(query.record().indexOf("dt_transaction")).toDate());
        money->setDescription(query.value(query.record().indexOf("description")).toString());
        money->setListMoneyCategory(getAllCategoryByMoney(money, listAllMoneyCategory));
        money->setTimestamp(query.value(query.record().indexOf("timestamp")).toString());

        listMoney.append(money);
    }

    qDebug() << "getting " << listMoney.length() << " money register ";

    return listMoney;
}

bool deleteMoney(Money* money)
{
    if (!money)
        return false;

    qDebug() << __PRETTY_FUNCTION__;

    // Deleting category related
    deleteAllCategoryByMoney(money);

    QString strQuery = " DELETE FROM money "
               " WHERE id_money = :id_money";

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":id_money", money->getIdMoney());

    return query.exec();
}

bool updateMoney(Money* money)
{
    if (!money)
        return false;

    qDebug() << __PRETTY_FUNCTION__;

    // Deleting category related
    deleteAllCategoryByMoney(money);

    QString strQuery = " UPDATE ";
    strQuery = strQuery + " money SET  amount = :amount, type_money = :type_money, "
               " dt_transaction = :dt_transaction, description = :description "
               " WHERE id_money = :id_money ";
//
//    QMessageBox messBox;
//    messBox.setText(strQuery + QString::number(, 10));
//    messBox.exec();

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":id_money", money->getIdMoney());
    query.bindValue(":dt_transaction", money->getDtTransaction());
    query.bindValue(":type_money", money->getTypeMoney() == Money::type_in ? 0 : 1);
    query.bindValue(":description", money->getDescription());
    query.bindValue(":amount", money->getAmount());
    bool result = query.exec();

    // Reinserting the categories
    registerAllCategoryByMoney(money);

    return result;
}

bool registerMoney(Money* money, int id_user)
{
    if (!money)
        return false;

    qDebug() << __PRETTY_FUNCTION__;

    money->setTimestamp(QDateTime::currentDateTime().toString());

    QString strQuery = " INSERT INTO ";
    strQuery = strQuery + " money (amount, type_money, dt_transaction, description, timestamp, id_user) values "
               "(:amount, :type_money, :dt_transaction, :description, :timestamp, :id_user) ";

    qDebug() << strQuery;

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":dt_transaction", money->getDtTransaction());
    query.bindValue(":type_money", money->getTypeMoney() == Money::type_in ? 0 : 1);
    query.bindValue(":description", money->getDescription());
    query.bindValue(":amount", money->getAmount());
    query.bindValue(":timestamp", money->getTimestamp());
    query.bindValue(":id_user", id_user);

    if( query.exec() )
        return registerAllCategoryByMoney(money);
    else return false;

    return true;
}

